﻿using AspNet.Identity.Dapper.Db;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AspNet.Identity.Dapper
{
    public class UserTable<TUser>
        where TUser:IdentityUser
    {
        private readonly DbManager _db;

        /// <summary>
        /// Constructor that takes a DbManager instance 
        /// </summary>
        /// <param name="database"></param>
        public UserTable(DbManager database)
        {
            this._db = database;
        }

        /// <summary>
        /// Returns the Member's name given a Member id
        /// </summary>
        /// <param name="memberId"></param>
        /// <returns></returns>
        public string GetUserName(Guid userId)
        {
            return _db.Connection.ExecuteScalar<string>("Select Name from User where Id=@userId", new { userId=userId.ToString() });
        }

        /// <summary>
        /// Returns a Member ID given a Member name
        /// </summary>
        /// <param name="userName">The Member's name</param>
        /// <returns></returns>
        public Guid GetUserIdByName(string userName)
        {
            return _db.Connection.ExecuteScalar<Guid>("Select Id from User where UserName=@UserName limit 1", new { UserName = userName });
        }

        /// <summary>
        /// Returns an TUser given the Member's id
        /// </summary>
        /// <param name="memberId">The Member's id</param>
        /// <returns></returns>
        public TUser GetUserById(Guid userId)
        {
            var id = userId.ToString();
            var user= _db.Connection.Query<TUser>("Select Id as IdString,Email,EmailConfirmed," +
                "PasswordHash,SecurityStamp,PhoneNumber,PhoneNumberConfirmed,TwoFactorEnabled," +
                "LockoutEndDateUtc,LockoutEnabled,AccessFailedCount,UserName from User where Id=@id", new { id })
                .FirstOrDefault();
            return user;
        }

        /// <summary>
        /// Returns a list of TUser instances given a Member name
        /// </summary>
        /// <param name="userName">Member's name</param>
        /// <returns></returns>
        public List<TUser> GetUserByName(string userName)
        {
            var user= _db.Connection.Query<TUser>("Select Id as IdString,Email,EmailConfirmed," +
                "PasswordHash,SecurityStamp,PhoneNumber,PhoneNumberConfirmed,TwoFactorEnabled," +
                "LockoutEndDateUtc,LockoutEnabled,AccessFailedCount,UserName from User where UserName=@UserName", new { UserName = userName })
                .ToList();
            return user;
        }

        public List<TUser> GetUserByEmail(string email)
        {
            return _db.Connection.Query<TUser>("Select Id as IdString,Email,EmailConfirmed," +
                "PasswordHash,SecurityStamp,PhoneNumber,PhoneNumberConfirmed,TwoFactorEnabled," +
                "LockoutEndDateUtc,LockoutEnabled,AccessFailedCount,UserName from User where Email=@email", new { email })
                .ToList();
        }

        /// <summary>
        /// Return the Member's password hash
        /// </summary>
        /// <param name="memberId">The Member's id</param>
        /// <returns></returns>
        public string GetPasswordHash(Guid userId)
        {
            var id = userId.ToString();
            var result= _db.Connection.ExecuteScalar<string>("Select PasswordHash from User where Id = @id", new { id });
            return result;
        }

        /// <summary>
        /// Sets the Member's password hash
        /// </summary>
        /// <param name="memberId"></param>
        /// <param name="passwordHash"></param>
        /// <returns></returns>
        public void SetPasswordHash(Guid userId, string passwordHash)
        {
            var id = userId.ToString();
            _db.Connection.Execute(@"
                    UPDATE
                        User
                    SET
                        PasswordHash = @pwdHash
                    WHERE
                        id = @Id", new { pwdHash = passwordHash, id });
        }

        /// <summary>
        /// Returns the Member's security stamp
        /// </summary>
        /// <param name="memberId"></param>
        /// <returns></returns>
        public string GetSecurityStamp(Guid userId)
        {
            var id = userId.ToString();
            return _db.Connection.ExecuteScalar<string>("Select SecurityStamp from User where Id = @id", new { id });
        }

        /// <summary>
        /// Inserts a new Member in the Users table
        /// </summary>
        /// <param name="Member"></param>
        /// <returns></returns>
        public bool Insert(TUser user)
        {
            //var id = Guid.NewGuid().ToString();
            return  _db.Connection.Execute(@"Insert into User
                                    (Id,UserName,  PasswordHash, SecurityStamp,Email,EmailConfirmed,PhoneNumber,PhoneNumberConfirmed, AccessFailedCount,LockoutEnabled,LockoutEndDateUtc,TwoFactorEnabled)
                            values  (@id,@name, @pwdHash, @SecStamp,@email,@emailconfirmed,@phonenumber,@phonenumberconfirmed,@accesscount,@lockoutenabled,@lockoutenddate,@twofactorenabled)
                          ",
                              new
                              {
                                  //id,
                                  Id=user.Id,
                                  name = user.UserName,
                                  pwdHash = user.PasswordHash,
                                  SecStamp = user.SecurityStamp,
                                  email = user.Email,
                                  emailconfirmed = user.EmailConfirmed,
                                  phonenumber = user.PhoneNumber,
                                  phonenumberconfirmed = user.PhoneNumberConfirmed,
                                  accesscount = user.AccessFailedCount,
                                  lockoutenabled = user.LockoutEnabled,
                                  lockoutenddate = user.LockoutEndDateUtc,
                                  twofactorenabled = user.TwoFactorEnabled
                              })>0;

        }

        /// <summary>
        /// Deletes a Member from the Users table
        /// </summary>
        /// <param name="memberId">The Member's id</param>
        /// <returns></returns>
        private bool Delete(Guid userId)
        {
            return _db.Connection.Execute(@"Delete from User where Id = @userId", new { userId = userId.ToString() })>0;
        }

        /// <summary>
        /// Deletes a Member from the Users table
        /// </summary>
        /// <param name="Member"></param>
        /// <returns></returns>
        public bool Delete(TUser user)
        {
           return Delete(user.Id);
        }

        /// <summary>
        /// Updates a Member in the Users table
        /// </summary>
        /// <param name="Member"></param>
        /// <returns></returns>
        public bool Update(TUser user)
        {
            return _db.Connection
              .Execute(@"
                            Update User set UserName = @userName, PasswordHash = @pswHash, SecurityStamp = @secStamp, 
                Email=@email, EmailConfirmed=@emailconfirmed, PhoneNumber=@phonenumber, PhoneNumberConfirmed=@phonenumberconfirmed,
                AccessFailedCount=@accesscount, LockoutEnabled=@lockoutenabled, LockoutEndDateUtc=@lockoutenddate, TwoFactorEnabled=@twofactorenabled  
                WHERE Id = @userId",
                new
                {
                    userId=user.Id,
                    userName = user.UserName,
                    pswHash = user.PasswordHash,
                    secStamp = user.SecurityStamp,
                    memberId = user.Id,
                    email = user.Email,
                    emailconfirmed = user.EmailConfirmed,
                    phonenumber = user.PhoneNumber,
                    phonenumberconfirmed = user.PhoneNumberConfirmed,
                    accesscount = user.AccessFailedCount,
                    lockoutenabled = user.LockoutEnabled,
                    lockoutenddate = user.LockoutEndDateUtc,
                    twofactorenabled = user.TwoFactorEnabled
                }
           )>0;
        }
    }
}
